﻿using Ayatta.Contract.Component;
using Ayatta.Service.Extension;
using Dapper;
using System;
using System.Text;
using System.Linq;
using System.Data;
using Ayatta.Contract;
using System.Xml.Linq;
using Ayatta.Contract.Domain;
using System.Collections.Generic;
using Ayatta.Common.Extensions;

namespace Ayatta.Service
{
    public class OrderService : ServiceBase, IOrderService
    {
        #region Order

        public bool CreateOrder(Order o)
        {
            var xElement = new XElement("xml");

            xElement.Add(new XElement("order",
                                      new XAttribute("Id", o.Id),
                                      new XAttribute("Category", (byte)o.Category),
                                      new XAttribute("Quantity", o.Quantity),
                                      new XAttribute("Postage", o.Postage.ToString("N")),
                                      new XAttribute("SubTotal", o.SubTotal.ToString("N")),
                                      new XAttribute("Discount", o.Discount.ToString("N")),
                                      new XAttribute("Total", o.Total.ToString("N")),
                                      new XAttribute("Paid", o.Paid.ToString("N")),
                                      new XAttribute("UseIntegral", o.UseIntegral),
                                      new XAttribute("RealUseIntegral", o.RealUseIntegral),
                                      new XAttribute("DonateIntegral", o.DonateIntegral),
                                      new XAttribute("Weight", o.Weight.ToString("N")),
                                      new XAttribute("PaymentType", (byte)o.PaymentType),
                                      new XAttribute("ShippingType", (byte)o.ShippingType),
                                      new XAttribute("ExpirationTime", o.ExpirationTime),
                                      new XAttribute("ETicketExtra", o.ETicketExtra),
                                      new XAttribute("MultipleConsign", o.MultipleConsign),
                                      new XAttribute("Receiver", o.Receiver),
                                      new XAttribute("Phone", o.Phone.EmptyIfNull()),
                                      new XAttribute("Mobile", o.Mobile.EmptyIfNull()),
                                      new XAttribute("AreaId", o.AreaId),
                                      new XAttribute("Province", o.Province),
                                      new XAttribute("City", o.City),
                                      new XAttribute("District", o.District),
                                      new XAttribute("Address", o.Address),
                                      new XAttribute("PostalCode", o.PostalCode),
                                      new XAttribute("HasInvoice", o.HasInvoice),
                                      new XAttribute("InvoiceCategory", o.InvoiceCategory),
                                      new XAttribute("InvoiceTitle", o.InvoiceTitle),
                                      new XAttribute("InvoiceContent", o.InvoiceContent),
                                      new XAttribute("IpAddress", o.IpAddress),
                                      new XAttribute("AssociatedId", o.AssociatedId),
                                      new XAttribute("TraceCode", o.TraceCode),
                                      new XAttribute("BuyerId", o.BuyerId),
                                      new XAttribute("BuyerNick", o.BuyerNick),
                                      new XAttribute("BuyerFlag", o.BuyerFlag),
                                      new XAttribute("BuyerMemo", o.BuyerMemo),
                                      new XAttribute("BuyerMessage", o.BuyerMessage),
                                      new XAttribute("BuyerRate", o.BuyerRate),
                                      new XAttribute("SellerId", o.SellerId),
                                      new XAttribute("SellerNick", o.SellerNick),
                                      new XAttribute("SellerFlag", o.SellerFlag),
                                      new XAttribute("SellerMemo", o.SellerMemo),
                                      new XAttribute("SellerRate", o.SellerRate),
                                      new XAttribute("Status", (byte)o.Status),
                                      new XAttribute("Extra", o.Extra),
                                      new XAttribute("ModifiedOn", o.ModifiedOn.ToString("yyyy-MM-dd HH:mm:ss:fff")),
                                      new XAttribute("CreatedBy", o.CreatedBy),
                                      new XAttribute("CreatedOn", o.CreatedOn.ToString("yyyy-MM-dd HH:mm:ss:fff"))
                             ));

            xElement.Add(new XElement("items", o.OrderItems.Select(x => new XElement("item",
                                                                                     new XAttribute("Id", x.Id),
                                                                                     new XAttribute("OrderId", x.OrderId),
                                                                                     new XAttribute("SkuId", x.SkuId),
                                                                                     new XAttribute("ItemId", x.ItemId),
                                                                                     new XAttribute("ProductId",x.ProductId),
                                                                                     new XAttribute("CategoryId",x.CategoryId),
                                                                                     new XAttribute("CategoryRootId", x.CategoryRootId),
                                                                                     new XAttribute("OuterId", x.OuterId),
                                                                                     new XAttribute("MealId", x.MealId),
                                                                                     new XAttribute("MealName",x.MealName),
                                                                                     new XAttribute("Title", x.Title),
                                                                                     new XAttribute("PictureUrl",x.PictureUrl),
                                                                                     new XAttribute("Properties", x.Properties),
                                                                                     new XAttribute("Quantity",x.Quantity),
                                                                                     new XAttribute("Price",x.Price.ToString("N")),
                                                                                     new XAttribute("Discount",x.Discount.ToString("N")),
                                                                                     new XAttribute("Adjust",x.Adjust.ToString("N")),
                                                                                     new XAttribute("Total",x.Total.ToString("N")),
                                                                                     new XAttribute("Paid",x.Paid.ToString("N")),
                                                                                     new XAttribute("ShippingType",(byte)x.ShippingType),
                                                                                     new XAttribute("BuyerId", x.BuyerId),
                                                                                     new XAttribute("SellerId",x.SellerId),
                                                                                     new XAttribute("IsGift", x.IsGift),
                                                                                     new XAttribute("IsService",x.IsService),
                                                                                    new XAttribute("IsVirtual",x.IsVirtual),
                                                                                     new XAttribute("ExpirationTime",x.ExpirationTime),
                                                                                     new XAttribute("BindId", x.BindId),
                                                                                     new XAttribute("LogisticsCompany",x.ShippingCompany),
                                                                                     new XAttribute("ExpressNo",x.ExpressNo),
                                                                                     new XAttribute("Status",(byte)x.Status),
                                                                                     new XAttribute("Extra", x.Extra),
                                                                                     new XAttribute("ModifiedOn",x.ModifiedOn.ToString("yyyy-MM-dd HH:mm:ss:fff")),
                                                                                     new XAttribute("CreatedBy",x.CreatedBy),
                                                                                     new XAttribute("CreatedOn",x.CreatedOn.ToString("yyyy-MM-dd HH:mm:ss:fff"))
                                                                            ))));

            var xml = xElement.ToString();
            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);
            param.Add("@status", false, DbType.Boolean, ParameterDirection.Output);
            try
            {
                using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
                {
                    connection.Execute("[Proc.Order.Create]", param, commandType: CommandType.StoredProcedure);
                    return param.Get<bool>("@status");
                }
            }
            catch (Exception e)
            {
                //System.IO.File.WriteAllText("E:/Ayatta/WebSite/Web.Wcf.Host/a.txt", xml);
                return false;
            }
        }

        public bool CreateGameOrder(Order o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Order.Table.Order)
            .Field("([Id],[Category],[Quantity],[Postage],[SubTotal],[Discount],[Total],[Paid],[UseIntegral],[RealUseIntegral],[DonateIntegral],[Weight],[PaymentType],[ShippingType],[PayTime],[ConsignTime],[EndTime],[ExpirationTime],[ETicketExtra],[MultipleConsign],[Receiver],[Phone],[Mobile],[AreaId],[Province],[City],[District],[Address],[PostalCode],[HasInvoice],[InvoiceCategory],[InvoiceTitle],[InvoiceContent],[IpAddress],[AssociatedId],[TraceCode],[BuyerId],[BuyerNick],[BuyerFlag],[BuyerMemo],[BuyerMessage],[BuyerRate],[SellerId],[SellerNick],[SellerFlag],[SellerMemo],[SellerRate],[Cancel],[CancelReason],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@Id,@Category,@Quantity,@Postage,@SubTotal,@Discount,@Total,@Paid,@UseIntegral,@RealUseIntegral,@DonateIntegral,@Weight,@PaymentType,@ShippingType,@PayTime,@ConsignTime,@EndTime,@ExpirationTime,@ETicketExtra,@MultipleConsign,@Receiver,@Phone,@Mobile,@AreaId,@Province,@City,@District,@Address,@PostalCode,@HasInvoice,@InvoiceCategory,@InvoiceTitle,@InvoiceContent,@IpAddress,@AssociatedId,@TraceCode,@BuyerId,@BuyerNick,@BuyerFlag,@BuyerMemo,@BuyerMessage,@BuyerRate,@SellerId,@SellerNick,@SellerFlag,@SellerMemo,@SellerRate,@Cancel,@CancelReason,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Id, o.Category, o.Quantity, o.Postage, o.SubTotal, o.Discount, o.Total, o.Paid, o.UseIntegral, o.RealUseIntegral, o.DonateIntegral, o.Weight, o.PaymentType, o.ShippingType, o.PayTime, o.ConsignTime, o.EndTime, o.ExpirationTime, o.ETicketExtra, o.MultipleConsign, o.Receiver, o.Phone, o.Mobile, o.AreaId, o.Province, o.City, o.District, o.Address, o.PostalCode, o.HasInvoice, o.InvoiceCategory, o.InvoiceTitle, o.InvoiceContent, o.IpAddress, o.AssociatedId, o.TraceCode, o.BuyerId, o.BuyerNick, o.BuyerFlag, o.BuyerMemo, o.BuyerMessage, o.BuyerRate, o.SellerId, o.SellerNick, o.SellerFlag, o.SellerMemo, o.SellerRate, o.Cancel, o.CancelReason, o.Extra, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });
            var template = builder.AddTemplate(SqlTemplate.Insert);
             using (var connection = GetConnection(ConnectionName.Order))
             {
                 return Execute(connection, template) == 1;
             }
        }

        /*
        public bool CreateOrder(Order o)
        {
            bool result;
            const string field = @"([Id],[UserId],[Category],[Quantity],[SubTotal],[ShippingCharge],[Discount],[Total],[Paid],[Refunded],[Weight],[ShippingMethod],[PaymentMethod],[PaidDate],[ShippingDate],[DeliveryDate],[OrderStatus],[ShippingStatus],[PaymentStatus],[AssociatedId],[Expiration],[AreaCode],[IpAddress],[TraceCode],[CreatedBy],[CreatedOn])
                                VALUES(@Id,@UserId,@Category,@Quantity,@SubTotal,@ShippingCharge,@Discount,@Total,@Paid,@Refunded,@Weight,@ShippingMethod,@PaymentMethod,@PaidDate,@ShippingDate,@DeliveryDate,@OrderStatus,@ShippingStatus,@PaymentStatus,@AssociatedId,@Expiration,@AreaCode,@IpAddress,@TraceCode,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Order.Table.Order).Field(field)
                .AddParameters(new { o.Id, o.UserId, o.Category, o.Quantity, o.SubTotal, o.ShippingCharge, o.Discount, o.Total, o.Paid, o.Refunded, o.Weight, o.ShippingMethod, o.PaymentMethod, o.PaidDate, o.ShippingDate, o.DeliveryDate, o.OrderStatus, o.ShippingStatus, o.PaymentStatus, o.AssociatedId, o.Expiration, o.AreaCode, o.IpAddress, o.TraceCode, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);

            var builderA = new SqlBuilder()
                .Table(Database.Order.Table.OrderItem)
                .Field("([OrderId],[UserId],[ProductId],[ProductName],[ProductVariantId],[ProductVariantName],[Quantity],[Discount],[UnitPrice],[ProductAttribute],[ProductAttributeXml],[CreatedBy],[CreatedOn])VALUES(@UserId,@OrderId,@ProductId,@ProductName,@ProductVariantId,@ProductVariantName,@Quantity,@Discount,@UnitPrice,@ProductAttribute,@ProductAttributeXml,@CreatedBy,@CreatedOn)");
            
            var templateA = builderA.AddTemplate(SqlTemplate.Insert);

            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    result = Execute(connection, template, transaction) == 1;
                    if (result)
                    {
                        ProcessOrder(o);
                        result = BatchInsert(connection, templateA.RawSql, o.OrderItems, transaction) > 0;
                    }
                    if (!result)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result = false;
                }
            }
            return result;

        }
        */

        public bool SetOrderAddressInvoice(string id, int userId, UserAddress address, UserInvoice invoice)
        {
            var builder = new SqlBuilder()
            .Table(Database.Order.Table.Order)
            .Field("[Receiver]=@Name", new { address.Name })
            .Field("[Phone]=@Phone", new { address.Phone })
            .Field("[Mobile]=@Mobile", new { address.Mobile })
            .Field("[AreaId]=@AreaId", new { address.AreaId })
            .Field("[Province]=@Province", new { address.Province })
            .Field("[City]=@City", new { address.City })
            .Field("[District]=@District", new { address.District })
            .Field("[Address]=@Address", new { address.Address })
            .Field("[PostalCode]=@PostalCode", new { address.PostalCode })
            .Where("[Id]=@Id AND [BuyerId]=@UserId", new { id, userId });

            if (invoice == null)
            {
                builder.Field("[HasInvoice]=0");
            }
            else
            {
                builder.Field("[InvoiceCategory]=@Category", new { invoice.Category })
                .Field("[InvoiceTitle]=@Title", new { invoice.Title })
                .Field("[InvoiceContent]=@Content", new { invoice.Content });
            }
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        public Order GetOrderMemo(string id, int userId, bool isSeller)
        {
            var builder = new SqlBuilder().Field("[Id]")
                                          .Table(Database.Order.Table.Order)
                                          .Where("Id=@Id", new { id });
            if (isSeller)
            {
                builder.Field("[SellerFlag],[SellerMemo]");

                builder.Where("[SellerId]=@UserId", new { userId });
            }
            else
            {
                builder.Field("[BuyerFlag],[BuyerMemo],[BuyerMessage]");
                builder.Where("[BuyerId]=@UserId", new { userId });
            }

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<Order>(connection, template).FirstOrDefault();
            }
        }

        public bool UpdateOrderBuyerMessage(string id, int userId, string message)
        {
            var builder = new SqlBuilder()
                .Field("[BuyerMessage]=@Message", new { message })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id AND [BuyerId]=@UserId", new { id, userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateOrderMemo(string id, int userId, bool isSeller, byte? flag, string memo)
        {
            var builder = new SqlBuilder()
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id", new { id });
            if (isSeller)
            {
                builder.Field("[SellerMemo]=@Memo", new { memo });
                if (flag.HasValue && flag.Value > 0)
                {
                    builder.Field("[SellerFlag]=@Flag", new { flag });
                }
                builder.Where("[SellerId]=@UserId", new { userId });
            }
            else
            {
                builder.Field("[BuyerMemo]=@Memo", new { memo });
                if (flag.HasValue && flag.Value > 0)
                {
                    builder.Field("[BuyerFlag]=@Flag", new { flag });
                }
                builder.Where("[BuyerId]=@UserId", new { userId });
            }

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateOrderAddress(string id, int userId, bool isSeller, UserAddress address)
        {
            var builder = new SqlBuilder()
                .Field("[Receiver]=@Name", new { address.Name })
                .Field("[Phone]=@Phone", new { address.Phone })
                .Field("[Mobile]=@Mobile", new { address.Mobile })
                .Field("[AreaId]=@AreaId", new { address.AreaId })
                .Field("[Province]=@Province", new { address.Province })
                .Field("[City]=@City", new { address.City })
                .Field("[District]=@District", new { address.District })
                .Field("[Address]=@Address", new { address.Address })
                .Field("[PostalCode]=@PostalCode", new { address.PostalCode })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id", new { id });
            builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        public Order GetOrderStatus(string id, int? userId, bool isSeller)
        {
            var builder = new SqlBuilder().Field("[Id],[Status],[Version]")
                                          .Table(Database.Order.Table.Order)
                                          .Where("Id=@Id", new { id });
            builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<Order>(connection, template).FirstOrDefault();
            }
        }

        public bool UpdateOrderStatus(string id, int userId, bool isSeller, OrderStatus status, byte[] version,
                                      string message = null)
        {
            var builder = new SqlBuilder()
                .Field("[Status]=@Status", new { status })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id AND [Version]=@Version", new { id, version }).
                 Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            if (status == OrderStatus.WaitSellerSend)
            {
                builder.Field("[PayTime]=getdate()");
            }
            if (status == OrderStatus.WaitBuyerConfirm)
            {
                builder.Field("[ConsignTime]=getdate()");
            }
            if (status == OrderStatus.Finished)
            {
                builder.Field("[EndTime]=getdate()");
            }
            if (status == OrderStatus.Canceled)
            {
                var cancel = (byte)(isSeller ? 2 : 1);
                builder.Field("[Cancel]=@Cancel", new { cancel });
                builder.Field("[CancelReason]=@Message", new { message });
            }
            //if(status==OrderStatus.Canceled)
            //{
            //    builder.Field("[CancelReason]=@Message",new{message});
            //}
            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateOrderExpiration(string id, int userId, int delay, byte[] version)
        {
            var builder = new SqlBuilder()
                .Field("[ExpirationTime]=CONVERT(date,dateadd(d,@delay,ExpirationTime),121)", new { delay = delay + 1 })
                .Table(Database.Order.Table.Order)
                .Where("Id=@Id AND [Version]=@Version", new { id, version }).
                 Where("[SellerId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteOrder(string id)
        {
            throw new NotImplementedException();
        }

        //public string GetAuctionOrderId(long userId, long auctionId)
        //{
        //    var builder = new SqlBuilder().Field("[Id]")
        //                                  .Table(Database.Order.Table.Order)
        //                                  .Where("[OrderStatus]<2 AND [PaymentStatus]=0")
        //                                  .Where("[UserId]=@UserId", new {userId})
        //                                  .Where("AssociatedId=@AssociatedId", new {associatedId = auctionId.ToString()});

        //    var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
        //    using (var connection = GetConnection(ConnectionName.Order))
        //    {
        //        return Query<string>(connection, template).FirstOrDefault();
        //    }
        //}

        public Order GetOrder(string id)
        {
            return GetOrder(id, null);
        }

        public Order GetOrder(string id, int? userId = null, bool inclItem = false, bool isSeller = false,
                              bool inclNote = false)
        {
            Order o;
            SqlBuilder.Template template;
            var builder = new SqlBuilder();

            var sb = new StringBuilder();
            if (userId.HasValue && userId.Value > 0)
            {
                if (isSeller)
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id AND [SellerId]=@UserId;",
                                    Database.Order.Table.Order);
                }
                else
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id AND [BuyerId]=@UserId;",
                                    Database.Order.Table.Order);
                }
            }
            else
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                Database.Order.Table.Order);
            }
            if (inclItem)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderItem);
            }

            if (inclNote)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderNote);
            }
            if (userId.HasValue && userId.Value > 0)
            {
                template = builder.AddTemplate(sb.ToString(), new { id, userId });

            }
            else
            {
                template = builder.AddTemplate(sb.ToString(), new { id });
            }

            using (var connection = GetConnection(ConnectionName.Order))
            using (var multi = connection.QueryMultiple(template))
            {
                o = multi.Read<Order>().FirstOrDefault();
                if (o != null)
                {
                    if (inclItem)
                    {
                        o.OrderItems = multi.Read<OrderItem>().ToList();
                    }
                    if (inclNote)
                    {
                        o.OrderNotes = multi.Read<OrderNote>().ToList();
                    }
                }
            }
            return o;
        }

        public PagedList<Order> GetPagedOrders(int index = 1, int size = 20, string id = null, int? userId = null,
                                              string nick = null, bool isSeller = false, string title = null,
                                              OrderCategory category = OrderCategory.Normal,
                                              OrderStatus? orderStatus = null, byte? commentStatus = null,
                                              DateTime? beginDate = null, DateTime? endDate = null)
        {
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Order.Table.Order)
            .Where("[Category]=@Category", new { category })
            .OrderBy("[CreatedOn] DESC")
            .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (!string.IsNullOrEmpty(id))
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(nick))
            {
                builder.Where(isSeller ? "[SellerNick]=@Nick" : "[BuyerNick]=@Nick", new { nick });
            }
            if (orderStatus.HasValue && orderStatus.Value >= 0)
            {
                builder.Where("[Status]=@OrderStatus", new { orderStatus });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);

            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            {
                return connection.PagedQuery<Order>(template);
            }
        }

        public PagedList<Order> Test()
        {
            var sql = @"declare @table table(row int,id varchar(26));
            with [temp.table] as (select row_number() over(/**orderby**/) as row,id from /**table**/ (nolock) /**where**/)
            insert into @table select * from [temp.table] ;
            select count(0) as [count] from @table ;
            select a.* from @table temp inner join [Order] a on temp.id=a.Id where temp.row between 1 and 5 ;
            select a.* from @table temp inner join [Order.Item] a on temp.id=a.OrderId where temp.row between 1 and 5 ;";
            var template = new SqlBuilder().AddTemplate(sql);
		/*
var sql=@"declare @table table(row int,id varchar(26),count int);
with [temp.table] as (select row_number() over(order by userid) as row,Userid from T_users where userid>720 and userid<1200)
,[Temp.Count] as (select count(0) [Count] from [temp.table])
insert into @table select * from [temp.table],[Temp.Count] WHERE row between 10 and 20
select top 1 [Count] as [Count] from @table
select b.* from @table as a inner join T_users b on b.UserId =a.Id
select b.* from @table as a inner join T_orderInfo b on b.UserId =a.Id";
*/
            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            using (var gridReader = connection.QueryMultiple(template))
            {
                return gridReader.ToPagedList<Order, OrderItem, string>(a => a.Id, b => b.OrderId, (a, b) => { a.OrderItems = b.ToList(); });
            }
        }

        public OrderItem GetOrderItem(string id, int userId, bool isSeller, bool inclOrder = false)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Order.Table.OrderItem + " a")
                .Where("a.[Id]=@Id", new {id})
                .Where(isSeller ? "a.[SellerId]=@UserId" : "a.[BuyerId]=@UserId", new {userId});
            if (inclOrder)
            {
                builder.InnerJoin(Database.Order.Table.Order + " b on a.[OrderId]=b.[Id]");
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            {
                if (inclOrder)
                {
                    return connection.Query<OrderItem, Order, OrderItem>(template.RawSql, (item, order) =>{item.Order = order;return item;}, template.Parameters)
                        .FirstOrDefault();
                }
                return Query<OrderItem>(connection, template).FirstOrDefault();
            }
        }

        public OrderItem GetOrderItemStatus(string id, int userId, bool isSeller)
        {
            var builder = new SqlBuilder().Field("[RefundId],[RefundStatus],[Status],[Version]")
            .Table(Database.Order.Table.OrderItem)
            .Where("a.[Id]=@Id", new { id })
            .Where(isSeller ? "a.[SellerId]=@UserId" : "a.[BuyerId]=@UserId", new { userId });
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<OrderItem>(connection, template).FirstOrDefault();
            }
        }

        public bool UpdateOrderItemRefundStatus(string id, int userId, bool isSeller, RefundStatus status,
                                                byte[] version, string refundId)
        {
            var builder = new SqlBuilder()
                .Field("[RefundStatus]=@Status", new { status })
                .Table(Database.Order.Table.OrderItem)
                .Where("Id=@Id AND [Version]=@Version", new { id, version }).
                 Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            if (!string.IsNullOrEmpty(refundId))
            {
                builder.Field("[RefundId]=@RefundId", new { refundId });
            }

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Execute(connection, template) == 1;
            }
        }

        private static bool UpdateOrderItemRefundStatusInner(IDbConnection connection, IDbTransaction transaction,
                                                             Refund o, bool isSeller)
        {
            var builder = new SqlBuilder()
            .Field("[RefundId]=@RefundId,[RefundStatus]=@Status", new { RefundId = o.Id, o.Status })
            .Table(Database.Order.Table.OrderItem)
            .Where("Id=@Id AND [Version]=@Version", new { Id = o.OrderItemId, o.Version }).
            Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId",new { userId = isSeller ? o.SellerId : o.BuyerId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            return connection.Execute(template, transaction) == 1;
        }


        public IList<OrderItem> GetOrderItems(string[] orderIds)
        {
            //var ids = orderIds.Aggregate((a, b) => "'" + a + "','" + b + "'").TrimEnd(',');
            var sb = new StringBuilder();
            foreach (var i in orderIds)
            {
                sb.Append("'" + i + "',");
            }
            var ids = sb.ToString().TrimEnd(',');
            var builder = new SqlBuilder().Field("*")
            .Table(Database.Order.Table.OrderItem)
            .Where("[OrderId] in (" + ids + ")");

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<OrderItem>(connection, template).ToList();
            }
        }

        public bool ConfirmOrder(string shippingId, int userId)
        {
            var param = new DynamicParameters();

            param.Add("@userId", userId, DbType.Int32);
            param.Add("@shippintId", shippingId, DbType.String);
            param.Add("@status", false, DbType.Boolean, ParameterDirection.Output);

            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            {
                connection.Execute("[Proc.Order.Confirm]", param, commandType: CommandType.StoredProcedure);
                return param.Get<bool>("@status");
            }
        }

        public bool OrderAutoFinished(UserTrade trade, Order order)
        {
            var now = DateTime.Now;
            if (!order.PayTime.HasValue)
            {
                order.PayTime = now;
            }
            var xElement = new XElement("xml");
            var time = now.ToString("yyyy-MM-dd HH:mm:ss:fff");
            
            xElement.Add(new XElement("order",
                                     new XAttribute("Id", order.Id),
                                     new XAttribute("PayTime", time),
                                     new XAttribute("EndTime", time),
                                     new XAttribute("ModifiedOn", time),
                                     new XAttribute("ConsignTime", time),
                                     new XAttribute("Status", (byte)order.Status)
                            ));
            xElement.Add(new XElement("items", order.OrderItems.Select(x => new XElement("item",
                                                                                         new XAttribute("Id", x.Id),
                                                                                         new XAttribute("EndTime", time),
                                                                                         new XAttribute("ModifiedOn", time),
                                                                                         new XAttribute("ConsignTime", time),
                                                                                         new XAttribute("ShippingType", (byte)x.ShippingType),
                                                                                         new XAttribute("Status", (byte)x.Status),
                                                                                         new XAttribute("Extra", x.Extra)
                                                                                ))));
            var xml = xElement.ToString();

            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);
            param.Add("@tradId", trade.Id, DbType.String);
            param.Add("@status", false, DbType.Boolean, ParameterDirection.Output);

            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            {
                connection.Execute("[Proc.Order.AutoFinished]", param, commandType: CommandType.StoredProcedure);
                return param.Get<bool>("@status");
            }
        }

        public OrderExchangeDetail GetOrderExchangeDetail(string orderId,int userId)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Order.Table.OrderExchangeDetail)
                .Where("OrderId=@OrderId", new { orderId })
                .Where("[BuyerId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            {
                return connection.Query<OrderExchangeDetail>(template).FirstOrDefault();
            }
        }

        #endregion

        #region Refund

        public bool CreateRefund(Refund o)
        {
            var result = false;
            var builder = new SqlBuilder()
                .Table(Database.Order.Table.Refund)
                .Field(
                    "([Id],[Category],[OrderId],[OrderItemId],[Total],[Return],[Payment],[BuyerId],[BuyerNick],[SellerId],[SellerNick],[SkuId],[ItemId],[ItemTitle],[ItemPicture],[Quantity],[Price],[ReasonId],[Reason],[Desc],[Attachments],[GoodsReturn],[GoodsReturnTime],[GoodsStatus],[ShippingId],[RefundAddress],[ShippingCompany],[ExpressNo],[Timeout],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@Id,@Category,@OrderId,@OrderItemId,@Total,@Return,@Payment,@BuyerId,@BuyerNick,@SellerId,@SellerNick,@SkuId,@ItemId,@ItemTitle,@ItemPicture,@Quantity,@Price,@ReasonId,@Reason,@Desc,@Attachments,@GoodsReturn,@GoodsReturnTime,@GoodsStatus,@ShippingId,@RefundAddress,@ShippingCompany,@ExpressNo,@Timeout,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {
                        o.Id,
                        o.Category,
                        o.OrderId,
                        o.OrderItemId,
                        o.Total,
                        o.Return,
                        o.Payment,
                        o.BuyerId,
                        o.BuyerNick,
                        o.SellerId,
                        o.SellerNick,
                        o.SkuId,
                        o.ItemId,
                        o.ItemTitle,
                        o.ItemPicture,
                        o.Quantity,
                        o.Price,
                        o.ReasonId,
                        o.Reason,
                        o.Desc,
                        o.Attachments,
                        o.GoodsReturn,
                        o.GoodsReturnTime,
                        o.GoodsStatus,
                        o.ShippingId,
                        o.RefundAddress,
                        o.ShippingCompany,
                        o.ExpressNo,
                        o.Timeout,
                        o.Extra,
                        o.Status,
                        o.ModifiedOn,
                        o.CreatedBy,
                        o.CreatedOn
                    });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var status = Execute(connection, template, transaction) == 1;
                    if (status)
                    {
                        status = UpdateOrderItemRefundStatusInner(connection, transaction, o, false);
                        if (status)
                        {
                            transaction.Commit();
                            result = true;
                        }
                        else
                        {
                            transaction.Rollback();
                        }
                    }
                }
                catch (Exception)
                {
                    transaction.Rollback();
                }
            }
            return result;
        }

        public Refund GetRefund(string id, int userId, bool isSeller = false)
        {
            var builder = new SqlBuilder().Field("*")
                                          .Table(Database.Order.Table.Refund)
                                          .Where("[Id]=@Id", new { id })
                                          .Where(isSeller ? "[SellerId]=@UserId" : "[BuyerId]=@UserId", new { userId });
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Order))
            {

                return Query<Refund>(connection, template).FirstOrDefault();
            }
        }

        #endregion

        #region Shipping

        public bool CreateShipping(Shipping o, bool flag, int userId, string orderId, OrderStatus orderStatus,
                                   byte[] version)
        {
            var extra = o.ShippingExtra;
            var xElement = new XElement("xml");

            xElement.Add(new XElement("shipping",
                                      new XAttribute("Id", o.Id),
                                      new XAttribute("OrderId", o.OrderId),
                                      new XAttribute("OrderItemIds", o.OrderItemIds),
                                      new XAttribute("ExpressNo", o.ExpressNo),
                                      new XAttribute("Title", o.Title),
                                      new XAttribute("Summary", o.Summary),
                                      new XAttribute("IsQuickCod", o.IsQuickCod),
                                      new XAttribute("FreightPayer", o.FreightPayer),
                                      new XAttribute("Receiver", o.Receiver),
                                      new XAttribute("Company", o.Company),
                                      new XAttribute("Phone", o.Phone),
                                      new XAttribute("Mobile", o.Mobile),
                                      new XAttribute("AreaId", o.AreaId),
                                      new XAttribute("Province", o.Province),
                                      new XAttribute("City", o.City),
                                      new XAttribute("District", o.District),
                                      new XAttribute("Address", o.Address),
                                      new XAttribute("PostalCode", o.PostalCode),
                                      new XAttribute("ShippingCompanyId", o.ShippingCompanyId),
                                      new XAttribute("ShippingCompany", o.ShippingCompany),
                                      new XAttribute("BuyerId", o.BuyerId),
                                      new XAttribute("BuyerNick", o.BuyerNick),
                                      new XAttribute("SellerId", o.SellerId),
                                      new XAttribute("SellerNick", o.SellerNick),
                                      new XAttribute("Status", (byte)o.Status),
                                      new XAttribute("Extra", o.Extra),
                                      new XAttribute("ModifiedOn", o.ModifiedOn),
                                      new XAttribute("CreatedBy", o.CreatedBy),
                                      new XAttribute("CreatedOn", o.CreatedOn)
                             ));
            xElement.Add(new XElement("shippingextra",
                                      new XAttribute("Id", extra.Id),
                                      new XAttribute("Sender", extra.Sender),
                                      new XAttribute("Company", extra.Company),
                                      new XAttribute("Phone", extra.Phone),
                                      new XAttribute("Mobile", extra.Mobile),
                                      new XAttribute("AreaId", extra.AreaId),
                                      new XAttribute("Province", extra.Province),
                                      new XAttribute("City", extra.City),
                                      new XAttribute("District", extra.District),
                                      new XAttribute("Address", extra.Address),
                                      new XAttribute("PostalCode", extra.PostalCode),
                                      new XAttribute("Refunder", extra.Refunder),
                                      new XAttribute("RefunderCompany", extra.RefunderCompany),
                                      new XAttribute("RefunderPhone", extra.RefunderPhone),
                                      new XAttribute("RefunderMobile", extra.RefunderMobile),
                                      new XAttribute("RefunderAreaId", extra.RefunderAreaId),
                                      new XAttribute("RefunderProvince", extra.RefunderProvince),
                                      new XAttribute("RefunderCity", extra.RefunderCity),
                                      new XAttribute("RefunderDistrict", extra.RefunderDistrict),
                                      new XAttribute("RefunderAddress", extra.RefunderAddress),
                                      new XAttribute("RefunderPostalCode", extra.RefunderPostalCode)
                             ));
            string xml = string.Empty;
            try
            {
                xml = xElement.ToString();

                var param = new DynamicParameters();
                param.Add("@xml", xml, DbType.Xml);
                param.Add("@flag", flag, DbType.Boolean);

                param.Add("@userId", userId, DbType.Int32);
                param.Add("@orderId", orderId, DbType.String);
                param.Add("@version", version, DbType.Binary);
                param.Add("@orderStatus", orderStatus, DbType.Int16);
                param.Add("@status", false, DbType.Boolean, ParameterDirection.Output);

                using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
                {
                    connection.Execute("[Proc.Shipping.Create]", param, commandType: CommandType.StoredProcedure);
                    return param.Get<bool>("@status");
                }
            }
            catch (Exception e)
            {
                System.IO.File.WriteAllText("E:/Ayatta/WebSite/Web.Wcf.Host/a.txt", xml);
                return false;
            }
        }

        public Shipping GetShipping(string id)
        {
            var builder = new SqlBuilder().Field("*")
                                          .Table(Database.Order.Table.Shipping)
                                          .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);

            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<Shipping>(connection, template).FirstOrDefault();
            }
        }

        public IList<Shipping> GetOrderShippings(string orderId)
        {
            var builder = new SqlBuilder().Field("*")
                                          .Table(Database.Order.Table.Shipping)
                                          .Where("[OrderId]=@OrderId", new { orderId });

            var template = builder.AddTemplate(SqlTemplate.Select);

            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<Shipping>(connection, template).ToList();
            }
        }

        public string GetSplitShippingOrderId(string orderId)
        {
            var result = string.Empty;
            List<string> array;
            var builder = new SqlBuilder().Field("[OrderItemIds]")
                                          .Table(Database.Order.Table.Shipping)
                                          .Where("[OrderId]=@OrderId", new { orderId });

            var template = builder.AddTemplate(SqlTemplate.Select);

            using (var connection = GetConnection(ConnectionName.Order))
            {
                array = Query<string>(connection, template).ToList();
            }
            if (array.Count > 0)
            {
                result = array.Aggregate(result, (current, o) => current + (o + ","));
                return result.TrimEnd(',');
            }
            return result;
        }

        #endregion

        #region Implementation of IService

        public int GetCachedSqlCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSql()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}